package com.sk.sk_frame_cloud.util;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * sql相关的工具类
 * @Author jiaok
 * @Date 2023-12-13 15:14
 */
@Slf4j
@Component
public class SqlFileUtil {

    /**
     * 加载数据库文件脚本
     */
    @Value("classpath:sql/sk_frame_demo.sql")
    private Resource schemaScript;

    /**
     * 创建新的数据库
     * @param connection 当前数据源连接
     * @param databaseName 目标数据库名称
     * @throws SQLException sql异常
     */
    public static void createDatabase(Connection connection,String databaseName) throws SQLException {

        log.info("===========================  开始创建 {} 数据库  ===========================",databaseName);

        //创建sql文本对象
        Statement statement = connection.createStatement();
        // 执行SQL语句，创建数据库并指定字符集
        String createDatabaseSql = "CREATE DATABASE "+databaseName+" CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci";
        statement.executeUpdate(createDatabaseSql);
        // 关闭资源
        statement.close();

        log.info("===========================  创建 {} 数据库结束  ===========================",databaseName);

    }

    /**
     * 执行指定的sql文件
     * @param connection 数据库连接
     * @param sqlFilePath sql文件路径
     * @throws SQLException sql执行异常
     * @throws IOException io读写异常
     */
    public static void executeSqlFile(Connection connection, String sqlFilePath) throws SQLException, IOException {
        log.info("===========================  开始执行数据库初始化DDL语句  ===========================");
        try (BufferedReader reader = new BufferedReader(new FileReader(sqlFilePath))) {
            StringBuilder sql = new StringBuilder();
            String line;
            while ((line = reader.readLine()) != null) {
                sql.append(line);
                if (line.trim().endsWith(";")) {
                    try (Statement statement = connection.createStatement()) {
                        statement.execute(sql.toString());
                        sql.setLength(0);
                    }
                }
            }
        }
        log.info("===========================  数据库初始化DDL语句 完成  ===========================");
    }


    /**
     * 执行指定的sql文件
     * @param connection 数据库连接
     * @param sqlFilePath sql文件路径
     * @throws SQLException sql执行异常
     * @throws IOException io读写异常
     */
    public  void initDatabase(DataSource dataSource, String sqlFilePath) throws SQLException, IOException {
        log.info("===========================  开始执行数据库初始化DDL语句  ===========================");
        final DataSourceInitializer initializer = new DataSourceInitializer();
        initializer.setDataSource(dataSource);
        initializer.setDatabasePopulator(getPopulator());
        log.info("===========================  数据库初始化DDL语句 完成  ===========================");
    }

    private DatabasePopulator getPopulator() {
        ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
        resourceDatabasePopulator.addScript(schemaScript);
        return resourceDatabasePopulator;
    }

}
